﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Data;
using System.Data.SqlClient;

namespace HRdex_Final.Forms
{
    public partial class WebForm4 : System.Web.UI.Page
    {
        DataTable dtbl = new DataTable("dtbl");
        SqlConnection conn = new SqlConnection("Data Source=.\\SQLEXPRESS;AttachDbFilename=|DataDirectory|\\HRdexDB.mdf;Integrated Security=True;User Instance=True");
        protected void Page_Load(object sender, EventArgs e)
        {
            SqlCommand cmd22 = new SqlCommand("SELECT EmployeeID, LastName + ', ' + FirstName + ' ' + MiddleName AS Name, DeptID, catPositionsID, CollegeID FROM formEmployees WHERE EmployeeID="+SessionID(), conn);
            SqlDataAdapter da22 = new SqlDataAdapter(cmd22);
            DataTable dt22 = new DataTable("dt22");

            da22.Fill(dt22);
           
            txtNo.Visible = false;
            //txtName.Enabled = false;
            ddlPos.Enabled = false;
            ddlDept.Enabled = false;
            txtName.Text = dt22.Rows[0].ItemArray[1].ToString();
            txtExplanation.Visible = false;
            Label1.Visible = false;
            ddlApprovalFac.Visible = false;
            lblpubDate.Text = Convert.ToString(DateTime.Now);

            Selected();
        }
        string SessionID()
        {
            return Session["EmployeeID"].ToString();
        }

        protected void btnSubmit_Click(object sender, EventArgs e)
        {
            string NoticeOfAbsenceID = Request.QueryString["NoticeOfAbsenceID"];

            if (txtNo.Text != NoticeOfAbsenceID)
            {
                SqlCommand cmd = new SqlCommand("INSERT INTO formNoticeofAbsence(EmpName, PosID, DeptID, ForEmpID, DateTime, Reason, pubDate) " +
                "VALUES (@empName, @pos,@Dept,@forEmp,@dt,@Reason,@pubDate)", conn);
                SqlDataAdapter adpt = new SqlDataAdapter(cmd);
                conn.Open();

                cmd.Parameters.Add("@empName", SqlDbType.VarChar).Value = txtName.Text;
                cmd.Parameters.Add("@pos", SqlDbType.VarChar).Value = ddlPos.SelectedValue;
                cmd.Parameters.Add("@Dept", SqlDbType.VarChar).Value = ddlDept.SelectedValue;
                cmd.Parameters.Add("@forEmp", SqlDbType.VarChar).Value = ddlToEmp.SelectedValue;
                cmd.Parameters.Add("@dt", SqlDbType.VarChar).Value = txtDate.Text + " " + ddlFHour.SelectedValue + ":" + ddlFMin.SelectedValue + " - " + ddlTHour.SelectedValue + ":" + ddlTmin.SelectedValue;
                cmd.Parameters.Add("@Reason", SqlDbType.VarChar).Value = txtReason.Text;
                cmd.Parameters.Add("@pubDate", SqlDbType.VarChar).Value = lblpubDate.Text;
                
                cmd.ExecuteNonQuery();
                conn.Close();
                ddlToEmp.Enabled = false;
                txtDate.Enabled = false;
                txtReason.Enabled = false;
                ddlFHour.Enabled = false;
                ddlFMin.Enabled = false;
                ddlTHour.Enabled = false;
                ddlTmin.Enabled = false;
                lblConfirmationMessage.Text = "** You successfully send new notice of absence to the faculty. Please wait for the response. Thank you! ";
            }
            else
            {
                Edit();
                notification();
                lblConfirmationMessage.Text = "You now give your remarks!";
            }
        }


        public void Selected()
        {
            string NoticeOfAbsenceID = Request.QueryString["NoticeOfAbsenceID"];

            if (NoticeOfAbsenceID != null)
            {
                SqlCommand cmd33 = new SqlCommand("SELECT EmpName, PosID, DeptID, ForEmpID, DateTime, Reason, pubDate, FacApproval, NoticeOfAbsenceID, remarksSend FROM formNoticeofAbsence WHERE (NoticeOfAbsenceID =" + NoticeOfAbsenceID + ")", conn);
                SqlDataAdapter da33 = new SqlDataAdapter(cmd33);
                DataTable dt33 = new DataTable("dt33");
                da33.Fill(dt33);
                txtName.Enabled = false;
                ddlDept.Enabled = false;
                ddlFHour.Visible = false;
                ddlFMin.Visible = false;
                ddlPos.Enabled = false;
                ddlTHour.Visible = false;
                ddlTmin.Visible = false;
                ddlToEmp.Enabled = false;
                txtDate.Visible = false;
                txtReason.Enabled = false;
                txtExplanation.Visible = true;
                Label1.Visible = true;
                ddlApprovalFac.Visible = false;


                txtNo.Text = dt33.Rows[0].ItemArray[8].ToString();
                txtName.Text = dt33.Rows[0].ItemArray[0].ToString();
                ddlDept.SelectedValue = dt33.Rows[0].ItemArray[2].ToString();
                ddlToEmp.SelectedValue = dt33.Rows[0].ItemArray[3].ToString();
                txtDate.Text = dt33.Rows[0].ItemArray[4].ToString();
                txtReason.Text = dt33.Rows[0].ItemArray[5].ToString();
                lblpubDate.Text = dt33.Rows[0].ItemArray[6].ToString();
                SqlCommand cmd123 = new SqlCommand("SELECT formEmployees.EmployeeID, catUserLevel.catUserLevelName, " +
                   "catPositions.catPositionsName, catDepartment.DepartmentName FROM formEmployees INNER " +
                   "JOIN catUserLevel ON formEmployees.UserLevelID = catUserLevel.UserLevelID INNER JOIN " +
                   "catPositions ON formEmployees.catPositionsID = catPositions.catPositionsID INNER " +
                   "JOIN catDepartment ON formEmployees.DeptID = catDepartment.DeptID WHERE (formEmployees.EmployeeID=" + SessionID() + ")", conn);
                SqlDataAdapter da123 = new SqlDataAdapter(cmd123);
                DataTable dt123 = new DataTable("dt123");
                da123.Fill(dt123);

                if (dt123.Rows[0].ItemArray[1].ToString() == "Faculty")
                {
                    ddlApprovalFac.Visible = true;
                    ddlApprovalFac.Enabled = true;
                    txtExplanation.Enabled = true;
                }
                else
                {
                    txtExplanation.Enabled = false;
                    ddlApprovalFac.Enabled = false;
                    ddlApprovalFac.Visible = true;
                }

                if (dt33.Rows[0].ItemArray[7].ToString() == "Denied")
                {
                    ddlApprovalFac.Visible = true;
                    ddlApprovalFac.SelectedValue = dt33.Rows[0].ItemArray[7].ToString();
                    txtExplanation.Text = dt33.Rows[0].ItemArray[9].ToString();
                    ddlApprovalFac.Enabled = false;
                    txtExplanation.Enabled = false;
                }
                else if (dt33.Rows[0].ItemArray[7].ToString() == "Approved")
                {
                    ddlApprovalFac.Visible = true;
                    ddlApprovalFac.SelectedValue = dt33.Rows[0].ItemArray[7].ToString();
                    txtExplanation.Text = dt33.Rows[0].ItemArray[9].ToString();
                    ddlApprovalFac.Enabled = false;
                    txtExplanation.Enabled = false;    
                }
                else
                {
                    ddlApprovalFac.Visible = true;
                    ddlApprovalFac.Enabled = true;
                    txtExplanation.Enabled = true;
                    Label1.Enabled = true;

                }

               
            }

        }
        public void Edit()
        {
            string NoticeOfAbsenceID = Request.QueryString["NoticeOfAbsenceID"];
            SqlCommand cmd123 = new SqlCommand("UPDATE formNoticeofAbsence SET FacApproval = @approval, remarksSend =@rSend, pubDateReturn =@dateReturn WHERE (NoticeOfAbsenceID=" + NoticeOfAbsenceID + ")", conn);
            SqlDataAdapter da123 = new SqlDataAdapter(cmd123);

            conn.Open();
            cmd123.Parameters.AddWithValue("@approval",  ddlApprovalFac.SelectedValue);
            cmd123.Parameters.AddWithValue("@rSend", txtExplanation.Text);
            cmd123.Parameters.AddWithValue("@dateReturn", DateTime.Now);
            cmd123.ExecuteNonQuery();
            conn.Close();
        }
        void notification()
        {
            //string formCBAID = Request.QueryString["NoticeOfAbsenceID"];
            //string desc = "Your Noticed of Absences has been " + ddlApprovalFac.SelectedItem.Text;
            //string sqlc = "INSERT INTO formMessegeSender " +
            //    "(Subject, Description, DateTime, EmployeeID, catMessegeID, fromEmpID, Readed) " +
            //    "VALUES (Notice of Absence," +
            //    desc + "," +
            //    DateTime.Now + "," +
            //    cbasender(formCBAID) +
            //    ",2," +
            //    SessionID() +
            //    ", 0)";
            //SqlCommand cmd = new SqlCommand(sqlc, conn);

            //conn.Open();
            //cmd.ExecuteNonQuery();
            //conn.Close();
        }

        //string cbasender(string src)
        //{
        //    //string sqlx = "SELECT EmployeeID FROM formNoticeofAbsence WHERE NoticeOfAbsenceID = " + src;
        //    //SqlDataAdapter cmd = new SqlDataAdapter(sqlx, conn);
        //    //DataTable tbl = new DataTable();

        //    //conn.Open();
        //    //cmd.Fill(tbl);
        //    //conn.Close();

        //    //return tbl.Rows[0][0].ToString();
        //}

        protected void ddlToEmp_SelectedIndexChanged(object sender, EventArgs e)
        {
            SqlCommand cmdName = new SqlCommand("SELECT EmployeeID, LastName + ', ' + FirstName + ' ' + MiddleName AS Name, DeptID, catPositionsID, CollegeID FROM formEmployees WHERE (EmployeeID =" + ddlToEmp.SelectedValue + ")", conn);
            SqlDataAdapter daName = new SqlDataAdapter(cmdName);
            DataTable dtName = new DataTable("dtName");
            daName.Fill(dtName);
            ddlDept.SelectedValue = dtName.Rows[0].ItemArray[2].ToString();
            ddlPos.SelectedValue = dtName.Rows[0].ItemArray[3].ToString();

        }

    }
}